package jmine.tec.environment.db.server;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import jmine.tec.environment.utils.BuildData;
import jmine.tec.persist.schema.api.SqlDialect;
import jmine.tec.persist.schema.impl.dialects.H2Dialect;

import org.apache.log4j.Logger;
import org.h2.tools.Server;

import bancosys.tec.utils.db.converter.DbConverter;
import bancosys.tec.utils.db.converter.OracleToH2Converter;
import bancosys.tec.utils.db.executor.DbExecutor;
import bancosys.tec.utils.db.executor.HsqlDbExecutor;

/**
 * @author piercio
 */
public class H2TestDBServer extends AbstractTestDBServer implements ManagedTestDBServer {

    private static final int MISECONDS_PER_SECOND = 1000;

    private static final Logger LOG = Logger.getLogger(HSQLTestDBServer.class);

    private static Map<String, Server> h2ServerInstances = new HashMap<String, Server>();

    /**
     * {@inheritDoc}
     */
    public void clearDBData(String... skipTables) throws SQLException {
        List<String> skipList = new LinkedList<String>();

        if (skipTables != null) {
            for (String element : skipTables) {
                skipList.add(element.toUpperCase());
            }
        }

        Connection conn = null;
        try {
            conn = this.getConnection();
            List<String> tables = this.getTableNames(conn);

            Statement st = conn.createStatement();

            /* Disable constraints check */
            st.executeUpdate("SET REFERENTIAL_INTEGRITY FALSE");

            /* Delete table data */
            for (String table : tables) {
                if (!skipList.contains(table.toUpperCase())) {
                    st.executeUpdate("DELETE FROM " + table);
                }
            }

            /* Enable constraints check */
            st.executeUpdate("SET REFERENTIAL_INTEGRITY TRUE");

        } catch (SQLException e) {
            throw new TestDBServerException(e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    public void dropTables() throws SQLException {
        Connection conn = null;
        try {
            conn = this.getConnection();
            List<String> tables = this.getTableNames(conn);

            Statement st = conn.createStatement();

            for (String table : tables) {
                st.executeUpdate("DROP TABLE " + table + " IF EXISTS CASCADE");
            }

            for (String sequence : this.getSequenceNames()) {
                st.executeUpdate("DROP SEQUENCE " + sequence);
            }
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * Devolve os nomes das sequences presentes no banco.
     * 
     * @return lista com os nomes das tabelas.
     * @throws SQLException caso ocorra algum erro.
     */
    protected List<String> getSequenceNames() throws SQLException {
        Connection conn = null;
        try {
            conn = this.getConnection();
            Statement st = conn.createStatement();

            ResultSet rs = st.executeQuery("SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES");

            List<String> sequences = new LinkedList<String>();
            try {
                while (rs.next()) {
                    sequences.add(rs.getString("SEQUENCE_NAME"));
                }
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException("Não foi possível obter nome das sequences", e);
            }
            return sequences;
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    public DbConverter getDbConverter(URL inputFile, File outputFile, String dbName) {
        return new OracleToH2Converter(inputFile, outputFile, dbName);
    }

    /**
     * {@inheritDoc}
     */
    public DbExecutor getDbExecutor() {
        return new HsqlDbExecutor();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String getDbName() {
        if (this.dataSource.getUrl().indexOf('/') > 0) {
            return super.getDbName();
        } else {
            return null;
        }
    }

    /**
     * {@inheritDoc}
     */
    public boolean isRunning() {
        Server instance = getServerInstance(this.dataSource.getUrl());
        if (instance == null) {
            return false;
        }

        return instance.isRunning(false);
    }

    /**
     * {@inheritDoc}
     */
    public void start() {
        /* Listen on a different port to avoid problems with the old persistence tests */
        if (!this.isRunning()) {
            String url = this.dataSource.getUrl();
            if (url.startsWith("jdbc:h2:tcp:")) {
                String dbName = this.getDbName();
                String[] args = {};
                if (dbName != null) {
                    String port = url.substring(url.lastIndexOf(':') + 1, url.lastIndexOf('/'));
                    args = new String[]{ "-tcpPort", port, "-tcpAllowOthers", "true", "-baseDir", System.getProperty("java.io.tmpdir") };
                    LOG.debug("starting server with props: " + Arrays.asList(args).toString());
                } else {
                    LOG.debug("starting server without custom props.");
                }

                try {
                    System.setProperty("h2.bindAddress", "127.0.0.1");
                    System.setProperty("h2.basedir", System.getProperty("java.io.tmpdir"));
                    Server instance = Server.createTcpServer(args);
                    instance.start();
                    h2ServerInstances.put(url, instance);
                } catch (SQLException e) {
                    throw new TestDBServerException("Não foi possível criar um novo servidor!", e);
                }
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    public void stop() {
        if (this.isRunning()) {
            getServerInstance(this.dataSource.getUrl()).stop();
        }
    }

    /**
     * {@inheritDoc}
     */
    public void save(String key) {
        Connection conn = null;
        try {
            conn = this.getConnection();

            Statement st = conn.createStatement();
            st.executeQuery("script to '" + this.getFilename(key) + "'");
        } catch (SQLException e) {
            throw new TestDBServerException(e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    public boolean restore(String key) {
        String filename = this.getFilename(key);
        if (!new File(filename).exists()) {
            return false;
        }

        LOG.debug("restoring server with key " + key);
        Connection conn = null;
        try {
            this.dropTables();

            conn = this.getConnection();
            Statement st = conn.createStatement();
            long start = System.currentTimeMillis();
            st.executeUpdate("runscript from '" + this.getFilename(key) + "'");
            LOG.debug("Tempo para recarregar o arquivo '" + this.getFilename(key) + "': "
                    + (((double) System.currentTimeMillis() - start) / MISECONDS_PER_SECOND));
            LOG.debug("server with key " + key + " restored successfully;");

            return true;
        } catch (SQLException e) {
            throw new TestDBServerException(e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new TestDBServerException(e);
            }
        }
    }

    /**
     * Devolve o nome do arquivo referenciado por essa chave.
     * 
     * @param key a chave.
     * @return o nome do arquivo.
     */
    private String getFilename(String key) {
        return BuildData.getDataDir() + key + ".h2.sql";
    }

    /**
     * Devolve a instância existente ou cria uma nova instância de servidor HSQL
     * 
     * @param connectionUrl url de conexão
     * @return uma instância de servidor HSQL
     */
    protected static Server getServerInstance(String connectionUrl) {
        return h2ServerInstances.get(connectionUrl);
    }

    /**
     * {@inheritDoc}
     */
    public SqlDialect getDialect() {
        SqlDialect dialect = new H2Dialect();
        dialect.setForceSequenceSupport(false);
        return dialect;
    }

    /**
     * {@inheritDoc}
     */
    public void enableBatchMode(Connection conn) {
        try {
            Statement st = conn.createStatement();
            st.executeUpdate("SET UNDO_LOG 0");
            st.executeUpdate("SET LOCK_MODE 0");
        } catch (SQLException e) {
            throw new TestDBServerException(e);
        }
    }

    /**
     * {@inheritDoc}
     */
    public void disableBatchMode(Connection conn) {
        try {
            Statement st = conn.createStatement();
            st.executeUpdate("SET UNDO_LOG 1");
            st.executeUpdate("SET LOCK_MODE 3");
        } catch (SQLException e) {
            throw new TestDBServerException(e);
        }
    }
}
